package com.example.ccj.u.sqlite;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Pair;

import com.alibaba.fastjson.JSON;

import com.example.ccj.u.JsonUtils;
import com.example.ccj.u.LogUtils;
import com.example.ccj.u.StringUtils;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class SqliteExcuteCommand {
    public static String TAG = "(Sqlite工具)SqliteExcuteCommand";

    public static SQLiteDatabase  getDB(Context context,String dbName){
        SqliteDatabaseHelper databaseHelper = new SqliteDatabaseHelper(context, dbName);//数据库对象
        return databaseHelper.getWritableDatabase();//获取可以写入数据的数据库对象
    }

    public static SQLiteDatabase getDB(Context context){
        SqliteDatabaseHelper databaseHelper = new SqliteDatabaseHelper(context, "ccj");//数据库对象
        return databaseHelper.getWritableDatabase();//获取可以写入数据的数据库对象
    }


    //表是否存在
    public static boolean isExistsOnTable(SQLiteDatabase db, String tableName){
        String sql = "select count(*) as c from sqlite_master where type ='table' and name =?";
        Cursor cursor = db.rawQuery(sql, new String[]{tableName});
        if(cursor.moveToNext()){
            int count = cursor.getInt(0);
            if(count>0){
                return true;
            }
        }
        cursor.close();
        return false;
    }


    //表是否存在
    public static boolean isExistsOnTable(Context context, String tableName){
        String sql = "select count(*) as c from sqlite_master where type ='table' and name =?";
        Cursor cursor = getDB(context).rawQuery(sql, new String[]{tableName});
        if(cursor.moveToNext()){
            int count = cursor.getInt(0);
            if(count>0){
                return true;
            }
        }
        cursor.close();
        return false;
    }



    /**
     * 建表
     * @param db
     * @param tableFieldType
     * 例如："id integer primary key autoincrement,num varchar(5),name varchar(24),creatdate varchar(20)"
     */
    public static void createTable(SQLiteDatabase db,String tableName,String tableFieldType) {
        if (isExistsOnTable(db,tableName)) {
            LogUtils.d(TAG,"表<"+tableName+">已存在，不需要再次创建！");
            return;
        }
        db.execSQL("CREATE TABLE IF NOT EXISTS "+tableName +"(" +tableFieldType +")");
        db.close();
        LogUtils.d(TAG, "createTable: 新建表" + tableName + "--> " + tableFieldType);
    }

    public static void createTable(Context context,String tableName,String tableFieldType) {
        SQLiteDatabase db = getDB(context);
        createTable(db,tableName,tableFieldType);
    }

    /**
     * 建表
     * 例如："id integer primary key autoincrement,num varchar(5),name varchar(24),creatdate varchar(20)"
     */
    public static void createTable(Context context,String tableName,List<TableBean> list) {
        SQLiteDatabase db = getDB(context);
        createTable(db,tableName,list);
    }

    /**
     * 反射建表
     * 例如："id integer primary key autoincrement,num varchar(5),name varchar(24),creatdate varchar(20)"
     */
    public static boolean createTable(Context context,Class<?> cls) {
        Pair<String,List<TableBean>> tableData = null;
        try {
            tableData = Reflection.getBeanTableFieldData(cls);
        } catch (IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        }
        if (tableData == null) {
            LogUtils.e(TAG, "createTable() 创建表失败: "+ "数据为空");
            return false;
        }
        if (tableData.first == null) {
            LogUtils.e(TAG, "createTable() 创建表失败: " + "表名为空");
            return false;
        }
        if (tableData.second == null) {
            LogUtils.e(TAG, "createTable() 创建表失败: " + "表数据为空");
            return false;
        }
        if (tableData.second.size() < 1) {
            LogUtils.e(TAG, "createTable() 创建表失败: " + "表数据为0");
            return false;
        }
        createTable(context,tableData.first,tableData.second);
        return true;
    }


    /**
     * 建表
     * 例如："id integer primary key autoincrement,num varchar(5),name varchar(24),creatdate varchar(20)"
     */
    public static void deleteTable(Context context,String tableName) {
        SQLiteDatabase db = getDB(context);
        deleteTable(db,tableName);
    }

    public static void deleteTable(Context context,Class<?> cls) {
        SQLiteDatabase db = getDB(context);
        deleteTable(db,Reflection.getTableName(cls));
    }


    /**
     * 建表
     * @param db
     * @param list
     * 例如："id integer primary key autoincrement,num varchar(5),name varchar(24),creatdate varchar(20)"
     */
    public static void createTable(SQLiteDatabase db,String tableName,List<TableBean> list) {
        if (isExistsOnTable(db,tableName)) {
            LogUtils.d(TAG,"表<"+tableName+">已存在，不需要再次创建！");
            return;
        }

//        db.execSQL("CREATE TABLE IF NOT EXISTS "+tableName +"(");
        Iterator<TableBean> iterator = list.iterator();
        StringBuilder builder = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        builder.append(tableName).append("(");
        while (iterator.hasNext()){
            TableBean bean = iterator.next();
            builder.append(bean.name).append(" ");
            builder.append(bean.type);
            if (bean.type.equals(TableBean.CHAR)) {
                builder.append("(").append(bean.length).append(") ");
            }
            if (bean.isKey) {
                if (bean.type.equals(TableBean.INT)) {
                    builder.append(" primary key autoincrement ");
                } else {
                    builder.append(" primary key ");
                }
            }
            if (bean.isNull) builder.append(" NOT NULL ");
            if (iterator.hasNext()) builder.append(",");
        }
        builder.append(")");
        LogUtils.d(TAG, "新建表sql语句:" + builder);
        db.execSQL(builder.toString());
        db.close();
    }

    public static void deleteTable(SQLiteDatabase db,String tableName){
        LogUtils.d(TAG,"删除表："+tableName);
        db.execSQL("drop table IF EXISTS " + tableName);
    }

    /**
     * 打印查询数据
     * @param datas
     */
    public static void printfSelectData(ArrayList<HashMap<String, String>> datas){
        StringBuilder builder = new StringBuilder("数据表数据：\n");
        for (HashMap<String ,String> list : datas) {
            for (Map.Entry<String, String> d : list.entrySet()) {
                builder.append(d.getKey()).append(":").append(d.getValue()).append(";");
            }
            builder.append("\n");
        }
        LogUtils.d("sqllite获取的数据：",builder.toString());
    }



    public static <T> List<T> get(Class<T> t, SQLiteDatabase db, boolean distinct, String tableName, String[] columns,
                                  String selection){
        Cursor cursor = db.query(distinct, tableName, columns, selection, null,
                null, null, null, null);
        return JsonUtils.toListBean(JSON.toJSONString(cursor),t);
    }

    /**
     * SQLite执行查询命令 获取单个字段的所有值
     * @param db
     * @param tableName 表名
     * @param column 要获取的列名
     * @param selection 条件
     * @return
     */
    public static List<String> selectOneField(SQLiteDatabase db,String tableName, String column, String selection){
        ArrayList<HashMap<String, String>>  ss = SqliteExcuteCommand.get(db,false,tableName,new String[]{column},selection);
        List<String> datas = new ArrayList<>();
        for (HashMap<String, String> map :ss) {
            if (map.containsKey(column)) {
                datas.add(map.get(column));
            }
        }
        LogUtils.i(TAG, "select: " + tableName +" ->  "+selection+" --> 查询到的数据条数：" + datas);
        return datas;
    }

    /**
     * 获取一条数据
     * @param context 上下文
     * @param cls 类型
     * @param selection 条件
     * @param selectionArgs 条件值
     * @param <T> 类型
     * @return 数据bean<T>
     */
    public static <T> T getOne(Context context, Class<T> cls, String selection,
                                  String... selectionArgs)  {
        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls), selection, selectionArgs);
        List<T> list = JsonUtils.toListBean(JSON.toJSONString(select), cls);
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 根据传入的类获取数据库数据
     * @param context 上下文
     * @param cls 类型
     * @param <T> 类型
     * @return 数据bean<T>
     */
    public static <T> List<T> get(Context context, Class<T> cls)  {
        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls), null, null);
        return JsonUtils.toListBean(JSON.toJSONString(select),cls);
    }

    public static <T> void getPrint(Context context, Class<T> cls)  {
        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls), null, null);
        printfSelectData(select);
    }

    /**
     * 根据传入的类获取数据库数据
     * @param context 上下文
     * @param cls 类型
     * @param selection 条件
     * @param selectionArgs 条件值
     * @param <T> 类型
     * @return 数据bean<T>
     */
    public static <T> List<T> get(Context context, Class<T> cls, String selection,
                                  String... selectionArgs)  {
        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls), selection, selectionArgs);
        return JsonUtils.toListBean(JSON.toJSONString(select),cls);
    }

    /**
     * 获取表单所有数据并根据字段排序
     * @param context 上下文
     * @param cls 类型
     * @param isAsc 排序语句 ASC|DESC  true为正序排列，false为倒序排列
     * @param orderField 排序字段
     * @return
     * @param <T>
     */
    public static <T> List<T> get(Context context, Class<T> cls, boolean isAsc,String orderField)  {
        String orderBy = orderField;
        if (!isAsc) {
            orderBy = orderField + " " + "DESC";
        }

        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls), orderBy);
        return JsonUtils.toListBean(JSON.toJSONString(select),cls);
    }


    public static <T> List<T> get(Context context, Class<T> cls, boolean isAsc,String orderField,String selection,
                                  String... selectionArgs)  {
        String orderBy = orderField;
        if (!isAsc) {
            orderBy = orderField + " " + "DESC";
        }


        ArrayList<HashMap<String, String>> select = get(context, Reflection.getTableName(cls),selection,selectionArgs, orderBy);
        return JsonUtils.toListBean(JSON.toJSONString(select),cls);
    }


    public static ArrayList<HashMap<String, String>> get(Context context, String tableName){
        return get(context,false,tableName,null,null);
    }

    public static ArrayList<HashMap<String, String>> get(Context context,
                                                         boolean distinct,
                                                         String tableName,
                                                         String[] columns,
                                                         String selection){

        return get(getDB(context),distinct,tableName,columns,selection);
    }

    public static ArrayList<HashMap<String, String>> get(Context context,
                                                         String table,
                                                         String selection,
                                                         String[] selectionArgs
                                                            ){
        return get(getDB(context),false,table,null,selection,selectionArgs,
                null,null,null,null);
    }

    public static ArrayList<HashMap<String, String>> get(Context context,
                                                         String table,
                                                         String selection,
                                                         String[] selectionArgs,
                                                         String orderBy){
        return get(getDB(context),false,table,null,selection,selectionArgs,
                null,null,orderBy,null);
    }

    public static ArrayList<HashMap<String, String>> get(Context context,
                                                         String table,
                                                         String orderBy){
        return get(getDB(context),false,table,null,null,null,
                null,null,orderBy,null);
    }


    /**
     * SQLite执行查询命令
     * @param db
     * @param distinct 是否返回唯一行 如果希望每行都是唯一的，则为true，否则为false。
     * @param tableName 表名
     * @param columns 要返回的列的列表
     * @param selection 条件筛选
     * @return
     */
    public static ArrayList<HashMap<String, String>> get(SQLiteDatabase db,
                                                         boolean distinct, String tableName, String[] columns,
                                                         String selection){

        ArrayList<HashMap<String, String>> list = new ArrayList<>();
        Cursor cursor = db.query(distinct, tableName, columns, selection, null,
                null, null, null, null);
        long result = 0;

        while (cursor.moveToNext()){
            HashMap<String,String> map = new HashMap<>();
            for (int i= 0; i<cursor.getColumnCount();i++){
                map.put(cursor.getColumnName(i),cursor.getString(i));
            }
            list.add(map);
            result++;
        }
        LogUtils.i(TAG, "select: " + tableName +" ->  "+selection+" --> 查询到的数据条数：" + result);
        return list;
    }

    public static void clearTable(Context context, Class<?> cls){
        getDB(context).execSQL("DELETE FROM " +Reflection.getTableName(cls));
    }

    public static void clearTable(Context context, String tableName){
        getDB(context).execSQL("DELETE FROM " +tableName);
    }

    public static void clearTable(SQLiteDatabase db, String tableName){
        LogUtils.i(TAG, "清空数据表: " + tableName);
        db.execSQL("DELETE FROM " +tableName);
    }


    public static ArrayList<HashMap<String, String>> get(Context context, String tableName,String[] columns,String selection,String orderBy, String limit){
        return get(getDB(context),true,tableName,columns,selection,null,null,null,orderBy,limit);
    }

    /**
     * SQLite执行查询命令
     * @param db
     * @param distinct 是否返回唯一行 如果希望每行都是唯一的，则为true，否则为false。
     * @param tableName 表名
     * @param columns 要返回的列的列表
     * @param selection 条件筛选
     * @param selectionArgs 替换条件中的？
     * @param groupBy 分组语句
     * @param having 分组筛选器
     * @param orderBy  排序语句
     * @param limit 最大返回的行数
     * @return
     */
    public static ArrayList<HashMap<String, String>> get(SQLiteDatabase db,
                                                         boolean distinct, String tableName, String[] columns,
                                                         String selection, String[] selectionArgs, String groupBy,
                                                         String having, String orderBy, String limit){

        ArrayList<HashMap<String, String>> list = new ArrayList<>();
        Cursor cursor = db.query(distinct, tableName, columns, selection, selectionArgs,
                groupBy, having, orderBy, limit);
        long result = 0;
        while (cursor.moveToNext()){
            HashMap<String,String> map = new HashMap<>();
            for (int i= 0; i<cursor.getColumnCount();i++){
                map.put(cursor.getColumnName(i),cursor.getString(i));
            }
            list.add(map);
            result++;
        }
        LogUtils.i(TAG, "select: " + tableName +" -> "+selection+" --> 查询到的数据条数：" + result);
        return list;
    }

    /**
     * SQLite插入数据
     */
    public static long replace(Context context,String tableName,ContentValues values){
        LogUtils.d(TAG,tableName+"-替换数据"+JSON.toJSONString(values.valueSet()));
        return getDB(context).replace(tableName, null, values);
    }

    /**
     * SQLite插入数据
     */
    public static long replace(SQLiteDatabase db,String tableName,ContentValues values){
        return db.replace(tableName, null, values);
    }

    /**
     * SQLite插入数据
     */
    public static long replace(Context context,Object obj){
        Pair<String, ContentValues> pair = Reflection.beanToSqlNameAndData(obj);
        if (pair.first == null) {
            LogUtils.e(TAG, "replace() 插入数据失败: " + "表名为空！");
        }
        SQLiteDatabase db = getDB(context);
        return db.replace(pair.first, null, pair.second);
    }

    /**
     * SQLite插入多条数据
     */
    public static <T> long replace(Context context, Collection<T> obj){
        SQLiteDatabase db = getDB(context);
        long lo = 0;
        for (T t : obj) {
            Pair<String, ContentValues> pair = Reflection.beanToSqlNameAndData(t);
            if (pair.first == null) LogUtils.e(TAG, "replace() 插入数据失败: " + "表名为空！");
            db.replace(pair.first, null, pair.second);
            lo++;
        }
        return lo;
    }

    /**
     * 插入行
     * @param context
     * @return 返回新行id
     */
    public static long save(Context context, Object obj){
        Pair<String, ContentValues> pair = Reflection.beanToSqlNameAndData(obj);
        if (pair.first == null) {
            LogUtils.e(TAG, "replace() 插入数据失败: " + "表名为空！");
        }
        return getDB(context).insert(pair.first,null,pair.second);
    }


    /**
     * 插入行
     * @param context
     * @return 返回新行id
     */
    public static long save(Context context, String tableName, Map<String,String> obj){
        ContentValues contentValues = new ContentValues();
        for (String key: obj.keySet()) {
            contentValues.put(key,obj.get(key));
        }
        return getDB(context).insert(tableName,null,contentValues);
    }


    /**
     * 插入行
     * @param context
     * @param tableName
     * @param contentValues
     * @return 返回新行id
     */
    public static long save(Context context, String tableName, ContentValues contentValues){
        LogUtils.d(TAG,tableName+"-插入数据"+JSON.toJSONString(contentValues.valueSet()));
        return getDB(context).insert(tableName,null,contentValues);
    }


    /**
     * SQLite插入数据
     * @param db
     * @param tableName
     * @param addData
     * @return 返回新行的id
     */
    public static long save(SQLiteDatabase db, String tableName, Map<String,String> addData){
        Cursor cursor =  db.rawQuery("PRAGMA TABLE_INFO('" +tableName +"')", null);
        ContentValues contentValues = new ContentValues();

        ArrayList<String> fields = getCursorFieldsToList(cursor,"name");
        for (Map.Entry<String, String> entry : addData.entrySet()) {
            if (fields.contains(entry.getKey())) {
                contentValues.put(entry.getKey(),entry.getValue());
            } else {
                LogUtils.e(TAG,"insert:表"+tableName+"不存在的字段-> " + entry.getKey()+"="+entry.getValue());
            }
        }
        //表名。空行数据，数据
        long result = db.insert(tableName,null,contentValues);
        LogUtils.i(TAG, "insert: " + tableName +" -> "+ contentValues.toString() +" --> 新增数量：" + result);
        return result;
    }

    /**
     * 删除
     */
    public static int delete(Context context, Class<?> cls, String whereClause, String... whereArgs){
        return getDB(context).delete(Reflection.getTableName(cls), whereClause,whereArgs);
    }

    /**
     * 删除
     */
    public static int delete(Context context, String table, String whereClause, String... whereArgs){
        return getDB(context).delete(table, whereClause,whereArgs);
    }

    /**
     * 删除
     * @param db
     * @param tableName
     * @param where
     */
    public static int delete(SQLiteDatabase db,String tableName,String... where){
        int result = db.delete(tableName, StringUtils.arrayToString(where),null);
        LogUtils.i(TAG, "delete: " + tableName +"-"+ StringUtils.arrayToString(where) +" --> 新增后表内记录数量：" + result);
        return result;
    }



    public static int update(Context context,Class<?> cls,ContentValues contentValues,String whereClause, String... whereArgs){
        return getDB(context).update(Reflection.getTableName(cls), contentValues, whereClause, whereArgs);
    }

    /**
     * 更新数据库
     * @param context 上下文
     * @param obj 数据
     * @param whereClause 条件
     * @param whereArgs 条件值
     * @return 更新条数
     */
    public static int update(Context context,Object obj,String whereClause, String... whereArgs){
        Pair<String, ContentValues> pair = Reflection.beanToSqlNameAndData(obj);
        return getDB(context).update(pair.first, pair.second, whereClause, whereArgs);
    }

    /**
     * 更新
     * @param where
     */
    public static int update(Context context, String tableName,ContentValues contentValues,String whereStr, String... where){
        LogUtils.i(TAG, "update: " + tableName +"-"+JSON.toJSONString(contentValues.valueSet())+"-"+ StringUtils.arrayToString(where));
        int update = getDB(context).update(tableName, contentValues, whereStr, where);
        return update;
    }

    /**
     * 更新
     * @param db
     * @param tableName
     * @param addData
     * @param where
     */
    public static int update(SQLiteDatabase db,String tableName,HashMap<String,String> addData,String... where){
        Cursor cursor =  db.rawQuery("PRAGMA TABLE_INFO('" +tableName +"')", null);
        ContentValues contentValues = new ContentValues();

        ArrayList<String> fields = getCursorFieldsToList(cursor,"name");
        for (Map.Entry<String, String> entry : addData.entrySet()) {
            if (fields.contains(entry.getKey())) {
                contentValues.put(entry.getKey(),entry.getValue());
            } else {
                LogUtils.e(TAG,"update:表"+tableName+"不存在的字段-> " + entry.getKey()+"="+entry.getValue());
//                LogUtils.writeLogFile("TAG","SqliteUpdate:表"+tableName+"不存在的字段-> " + entry.getKey()+"="+entry.getValue());
            }
        }

        int result = db.update(tableName, contentValues, StringUtils.arrayToString(where),null);
        LogUtils.i(TAG, "update: " + tableName +"-"+contentValues.toString()+"-"+ StringUtils.arrayToString(where) + " --> 更新数量：" + result);
        return result;
    }

    @SuppressLint("Range")
    private static ArrayList<String> getCursorFieldsToList(Cursor cursor, String field){
        ArrayList<String> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            list.add(cursor.getString(cursor.getColumnIndex(field)));
        }
        return list;
    }

}
